﻿using DocumentFormat.OpenXml.Lite.Core.Office;
using DML = DocumentFormat.OpenXml.Lite.DrawingML;
using DMLC = DocumentFormat.OpenXml.Lite.DrawingML.Chart;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Drawing;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Base;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Types;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.WorksheetElements;
using Samples.Drawing;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Lite.DrawingML.Geometry.Properties;

namespace Samples.Excel
{
    class Charts
    {
        public static void Create()
        {
            Workbook wb = DocumentManager.Instance.CreateSpreadsheet();
            Worksheet ws = wb.CreateWorksheet("Sheet1", null);

            #region Create Chart Descriptor

            ChartDescriptor descriptor = new ChartDescriptor()
            {
                Unit = "%",
                Title = "Sample Chart",
                Series = new List<Serie>(),
                XValues = new string[] { "Category1", "Category2", "Category3", "Category4" }
            };

            Serie s1 = new Serie()
            {
                Label = "Serie1",
                Color = System.Drawing.Color.Blue,
                References = new Dictionary<string, string>(),
                YValues = new string[] { "30", "10", "40", "20" }
            };

            Serie s2 = new Serie()
            {
                Label = "Serie2",
                Color = System.Drawing.Color.Orange,
                References = new Dictionary<string, string>(),
                YValues = new string[] { "13.5", "24.5", "12", "50" }
            };

            descriptor.Series.Add(s1);
            descriptor.Series.Add(s2);

            #endregion

            #region Data

            for (int i = 0; i < descriptor.XValues.Length; ++i)
            {
                Cell c = ws.CreateCell(i + 1, 1);
                c.CellType = ST_CellType.String;
                c.AddSimpleText(descriptor.XValues[i]);
            }

            for (int i = 0; i < descriptor.Series.Count; ++i)
            {
                Serie serie = descriptor.Series[i];
                for (int j = 0; j < serie.YValues.Length; ++j)
                {
                    Cell c = ws.CreateCell(j + 1, i + 2);
                    c.CellType = ST_CellType.Number;
                    c.AddSimpleText(serie.YValues[j]);
                }

                serie.References.Add("x", "Sheet1!$A$1:$A$4");
                serie.References.Add("y", "Sheet1!$" + Cell.Resolve(i + 2) + "$1:$" +
                    Cell.Resolve(i + 2) + "$" + serie.YValues.Length.ToString() + "");
            }

            #endregion

            WorksheetDrawing drawing = ws.CreateDrawing();
            drawing.AddNamespace("c", DML.Constants.Chart);
            string chartId = drawing.CreateChartElement(SampleChartBuilder.CreateChart(descriptor));
            CreateDrawingElement(drawing, new DMLC.Chart() { RelationshipId = chartId }, 0, 5, 12, 23);

            wb.Save(@"samples\chart.xlsx");

            Workbook parsed = null;
            if (DocumentManager.Instance.OpenExcelDocument(@"samples\chart.xlsx", out parsed))
            {
                DMLC.ChartSpace chartSpace = parsed.FindElementFor<DMLC.ChartSpace>(parsed.Worksheets[0].DrawingElement, chartId);
                Console.WriteLine("Sample created and parsed succesfully!");
            }
            Console.ReadLine();
        }

        public static void CreateDrawingElement(WorksheetDrawing drawing,
            object drawingElement, int cellFrom, int rowFrom, int cellTo, int rowTo)
        {
            drawing.CellAnchors.Add(new TwoCellAnchor()
            {
                From = new Point()
                {
                    Column = new Text<int>(cellFrom),
                    ColumnOffset = new Text<long>(190500),
                    Row = new Text<int>(rowFrom),
                    RowOffset = new Text<long>(138112)
                },
                To = new Point()
                {
                    Column = new Text<int>(cellTo),
                    ColumnOffset = new Text<long>(495300),
                    Row = new Text<int>(rowTo),
                    RowOffset = new Text<long>(23812)
                },
                Graphic = new GraphicFrame()
                {
                    Properties = new GraphicFrameProperties()
                    {
                        NonVisualProperties = new NonVisualProperties()
                        {
                            Id = 1,
                            Name = "Chart" + new Random().Next(100).ToString()
                        },
                        NonVisualFrameProperties = new NonVisualGraphicFrameDrawingProperties()
                    },
                    Transformation = new Transform2d()
                    {
                        Offset = new SimplePosition(0, 0),
                        Extents = new Extent(0, 0)
                    },
                    Graphic = new Graphic()
                    {
                        Data = new GraphicData()
                        {
                            Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart",
                            DrawingElement = drawingElement
                        }
                    }
                },
                ClientData = new ClientData()
            });
        }
    }
}
